package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QEnterQueueDao
{
  static Logger logger = LoggerFactory.getLogger(QEnterQueueDao.class);
  public static final String TABLE = "da_q_enter_queue";
  
  public static boolean insert(String userId, String nbxh, String zch, String qymc, String djjg, String qylx)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "INSERT INTO da_q_enter_queue (userid,nbxh,zch,qymc,djjg,qylx) VALUES(?,?,?,?,?,?)";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, userId);
      pstmt.setString(i++, nbxh);
      pstmt.setString(i++, zch);
      pstmt.setString(i++, qymc);
      pstmt.setString(i++, djjg);
      pstmt.setString(i++, qylx);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean delete(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "DELETE FROM da_q_enter_queue WHERE userid= '" + 
      userId + "' ";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean delete(String userId, String nbxh)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "DELETE FROM da_q_enter_queue WHERE userid= '" + 
      userId + "' and nbxh='" + nbxh + "'";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static List load(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = 
      "SELECT * FROM da_q_enter_queue WHERE userId = '" + 
      userId + "'";
    logger.debug(sql);
    List list = new ArrayList();
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      
      while (rs.next()) {
        list.add(rsToBean(rs));
      }
      return list;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return list;
  }
  
  public static QEnterQueue loadByNbxh(String userId, String nbxh)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = 
      "SELECT * FROM da_q_enter_queue WHERE userId='" + 
      userId + "' and nbxh = '" + nbxh + 
      "'";
    logger.debug(sql);
    QEnterQueue qenterqueue = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        qenterqueue = rsToBean(rs);
      }
      QEnterQueue localQEnterQueue1 = qenterqueue;
      return localQEnterQueue1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
    	DbClose.freeResultSet(rs);
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      
    }
    
    return null;
  }
  
  public static int count(String userId, String qymc, String nbxh, String zch)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT count(*) FROM (SELECT rownum r, da_q_enter_queue.* FROM da_q_enter_queue WHERE 1=1 ";
    if ((userId != null) && (userId.length() > 0)) {
      sql = 
        sql + " AND userId LIKE ? ";
    }
    if ((qymc != null) && (qymc.length() > 0)) {
      sql = 
        sql + " AND qymc LIKE ? ";
    }
    if ((nbxh != null) && (nbxh.length() > 0)) {
      sql = 
        sql + " AND nbxh LIKE ? ";
    }
    if ((zch != null) && (zch.length() > 0)) {
      sql = 
        sql + " AND zch LIKE ? ";
    }
    logger.debug(sql);
    int j = 0;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userId != null) && (userId.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userId + "%");
      }
      if ((qymc != null) && (qymc.length() > 0)) {
        pstmt.setString(i++, 
          "%" + qymc + "%");
      }
      if ((nbxh != null) && (nbxh.length() > 0)) {
        pstmt.setString(i++, 
          "%" + nbxh + "%");
      }
      if ((zch != null) && (zch.length() > 0)) {
        pstmt.setString(i++, 
          "%" + zch + "%");
      }
      rs = pstmt.executeQuery();
      if (!rs.next()) {
        j = rs.getInt(1);
      }
      i = j;
      return i;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return 0;
  }
  
  public static List load(String userId, String qymc, String nbxh, String zch, int page, int pageSize)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM (SELECT rownum r, da_q_enter_queue.* FROM da_q_enter_queue WHERE 1=1 ";
    if ((userId != null) && (userId.length() > 0)) {
      sql = 
        sql + " AND userId LIKE ? ";
    }
    if ((qymc != null) && (qymc.length() > 0)) {
      sql = 
        sql + " AND qymc LIKE ? ";
    }
    if ((nbxh != null) && (nbxh.length() > 0)) {
      sql = 
        sql + " AND nbxh LIKE ? ";
    }
    if ((zch != null) && (zch.length() > 0)) {
      sql = 
        sql + " AND zch LIKE ? ";
    }
    if (pageSize > 1) {
      sql = 
        sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
    }
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userId != null) && (userId.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userId + "%");
      }
      if ((qymc != null) && (qymc.length() > 0)) {
        pstmt.setString(i++, 
          "%" + qymc + "%");
      }
      if ((nbxh != null) && (nbxh.length() > 0)) {
        pstmt.setString(i++, 
          "%" + nbxh + "%");
      }
      if ((zch != null) && (zch.length() > 0)) {
        pstmt.setString(i++, 
          "%" + zch + "%");
      }
      if (pageSize > 1)
      {
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
      }
      rs = pstmt.executeQuery();
      List list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean(rs));
      }
      List list1 = list;
      List localList1 = list;
      return localList1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  static QEnterQueue rsToBean(ResultSet rs)
    throws SQLException
  {
    QEnterQueue bean = new QEnterQueue();
    bean.setNbxh(rs.getString("nbxh"));
    bean.setQymc(rs.getString("qymc"));
    bean.setUserId(rs.getString("userid"));
    bean.setZch(rs.getString("zch"));
    bean.setQylx(rs.getString("qylx"));
    return bean;
  }
}
